package com.turing.post.cardmanagement.impl;

import java.math.BigInteger;
import java.util.List;

import javax.ejb.EJB;
import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.cardmanagement.util.CardManaApplicationException;
import com.turing.post.cardmanagement.util.TransferInOutStockOrder;
import com.turing.post.cardmanagement.util.TransferInOutStockOrderDao;
import com.turing.post.organization.util.SysBranch;
import com.turing.post.organization.util.SysBranchDao;
import com.turing.post.util.Config;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

/**
 * 
 * @author YangShuo 卡调拨表接口实现类
 * 
 */
@Stateless(mappedName = "cardmanagement.impl.TransferInOutStockOrderDaoImpl")
@Remote
@Local
public class TransferInOutStockOrderDaoImpl implements
		TransferInOutStockOrderDao {
	/**
	 * 实体管理器
	 */
	@PersistenceContext
	protected EntityManager _em;

	@EJB(mappedName = "organization.impl.SysBranchDaoImpl")
	private SysBranchDao SysBranchDaoImpl;

	@SuppressWarnings("unchecked")
	@Override
	public List<TransferInOutStockOrder> getTransferInOutStockOrderList(
			String fromsysbranch, String tosysbranch, String starttime,
			String endtime, String transferStatus, Integer page) {
		String sql = "select * from TransferInOutStockOrder where 1=1 ";
		if (!PCPSUtil.isEmpty(fromsysbranch))
			sql = sql + "and (FromIssueBranchNo='" + fromsysbranch
					+ "' or FromSaleBranchNo='" + fromsysbranch + "') ";

		if (!PCPSUtil.isEmpty(tosysbranch))
			sql = sql + "and (ToIssueBranchNo='" + tosysbranch
					+ "' or ToSaleBranchNo='" + tosysbranch + "') ";

		if (!PCPSUtil.isEmpty(starttime) && !PCPSUtil.isEmpty(endtime))
			sql = sql + "and TranTime between to_date('" + starttime
					+ "','yyyy-mm-dd hh24:mi:ss') and to_date('" + endtime
					+ "','yyyy-mm-dd hh24:mi:ss') ";
		if (!PCPSUtil.isEmpty(transferStatus))
			sql = sql + "and TransferStatus='" + transferStatus + "' ";
		sql = sql + "order by TranTime desc";
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;		
		Query query = _em.createNativeQuery(sql, TransferInOutStockOrder.class);
		query.setFirstResult(startPosition);
		query.setMaxResults(maxResult);
		return query.getResultList();
	}

	@Override
	public Integer getTransferInOutStockOrderCount(String fromsysbranch,
			String tosysbranch, String starttime, String endtime,
			String transferStatus) {
		String sql = "select nvl(count(*),0) from TransferInOutStockOrder where 1=1 ";
		if (!PCPSUtil.isEmpty(fromsysbranch))
			sql = sql + "and (FromIssueBranchNo='" + fromsysbranch
					+ "' or FromSaleBranchNo='" + fromsysbranch + "') ";

		if (!PCPSUtil.isEmpty(tosysbranch))
			sql = sql + "and (ToIssueBranchNo='" + tosysbranch
					+ "' or ToSaleBranchNo='" + tosysbranch + "') ";

		if (!PCPSUtil.isEmpty(starttime) && !PCPSUtil.isEmpty(endtime))
			sql = sql + "and TranTime between to_date('" + starttime
					+ "','yyyy-mm-dd hh24:mi:ss') and to_date('" + endtime
					+ "','yyyy-mm-dd hh24:mi:ss') ";
		if (!PCPSUtil.isEmpty(transferStatus))
			sql = sql + "and TransferStatus='" + transferStatus + "'";
		Query query = _em.createNativeQuery(sql);
		return Integer.parseInt(query.getSingleResult().toString());
	}

	/**
	 * @根据状态及sysbranch查询发出方的调拨单集合
	 * @param sysbranch
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<TransferInOutStockOrder> getFromTransferInOutStockOrderByTransferInOutStockOrderIDandStatus(
			String sysbranch, String status) throws PCPSApplicationException {
		// 判断sysbranch是发卡商机构还是售卡机构
		String sql;
		if (SysBranchDaoImpl.getSysBranchByBranchNo(sysbranch).equals(
				SysBranch.SYSBRANCH_ISSUEBRANCH))
			sql = "select * from TransferInOutStockOrder where fromissuebranchno='"
					+ sysbranch + "' and TransferStatus='" + status + "'";
		else if (SysBranchDaoImpl.getSysBranchByBranchNo(sysbranch).equals(
				SysBranch.SYSBRANCH_SALEBRANCH))
			sql = "select * from TransferInOutStockOrder where fromsalebranchno='"
					+ sysbranch + "' and TransferStatus='" + status + "'";
		else
			throw new CardManaApplicationException(
					PCPSApplicationException.ERRCODE_CATEGORY_APPLICATION, "",
					"机构号码异常");
		Query query = _em.createNativeQuery(sql, TransferInOutStockOrder.class);
		return query.getResultList();
	}

	/**
	 * @根据状态及sysbranch查询接收方的调拨单集合
	 * @param sysbranch
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<TransferInOutStockOrder> getToTransferInOutStockOrderByTransferInOutStockOrderIDandStatus(
			String sysbranch, String status) throws PCPSApplicationException {
		String sql;
		if (SysBranchDaoImpl.getSysBranchByBranchNo(sysbranch).equals(
				SysBranch.SYSBRANCH_ISSUEBRANCH))
			sql = "select * from TransferInOutStockOrder where toissuebranchno='"
					+ sysbranch + "' and TransferStatus='" + status + "'";
		else if (SysBranchDaoImpl.getSysBranchByBranchNo(sysbranch).equals(
				SysBranch.SYSBRANCH_SALEBRANCH))
			sql = "select * from TransferInOutStockOrder where tosalebranchno='"
					+ sysbranch + "' and TransferStatus='" + status + "'";
		else
			throw new CardManaApplicationException(
					PCPSApplicationException.ERRCODE_CATEGORY_APPLICATION, "",
					"机构号码异常");
		Query query = _em.createNativeQuery(sql, TransferInOutStockOrder.class);
		return query.getResultList();
	}

	/**
	 * @根据TransferInOutStockOrderId主键查询
	 * @param TransferInOutStockOrderId
	 * @return
	 */
	@Override
	public TransferInOutStockOrder getTransferInOutStockOrderByTransferInOutStockOrderId(
			BigInteger TransferInOutStockOrderId) {
		return _em.find(TransferInOutStockOrder.class,
				TransferInOutStockOrderId);
	}

	/**
	 * @新增调拨单
	 * @param SaleCompany
	 */
	@Override
	public void insertTransferInOutStockOrder(
			TransferInOutStockOrder TransferInOutStockOrder)
			throws PCPSApplicationException {
		GeneralDao.insert(_em, TransferInOutStockOrder);
	}

	/**
	 * @修改调拨单
	 * @param TransferInOutStockOrder
	 */
	@Override
	public void updateTransferInOutStockOrder(
			TransferInOutStockOrder TransferInOutStockOrder)
			throws PCPSApplicationException {
		GeneralDao.update(_em, TransferInOutStockOrder);
	}

	/**
	 * @修改CancelTransferInOutStockOrderId
	 * @param TransferInOutStockOrderId
	 * @param CancelTransferInOutStockOrderId
	 */
	@Override
	public Integer updateTransferInOutStockOrderCancelID(
			BigInteger TransferInOutStockOrderId,
			BigInteger CancelTransferInOutStockOrderId)
			throws PCPSApplicationException {
		String sql = "update TransferInOutStockOrder set CancelTransferInOutStockOrderId="
				+ CancelTransferInOutStockOrderId
				+ " where TransferInOutStockOrderId="
				+ TransferInOutStockOrderId;
		Query query = _em.createNativeQuery(sql);
		return query.executeUpdate();
	}

	/**
	 * 卡-6 卡库存调拨统计表
	 * 
	 * @param webInput
	 * @param tranDate
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getTransferInOutStockReportByCondition(
			PCPSWebInput webInput, String fromBranchNo,String toBranchNo, String isSingle,String ssSingle,
			String startTime, String endTime,String transferStatus) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo()) ||PCPSUtil.isEmpty(fromBranchNo) ||PCPSUtil.isEmpty(toBranchNo) ||PCPSUtil.isEmpty(transferStatus) )
			return null;
		String issuecompanyno=webInput.getOperBranchNo().substring(0,6);
     
		String fromIssueBrchNo =" a.FromIssueBranchNo ='" + fromBranchNo + "'";
		String fromSaleBrchNo = " a.FromSaleBranchNo ='" + fromBranchNo + "'";
		if (isSingle.equals("0")) {
			fromIssueBrchNo=" a.FromIssueBranchNo in (select subBranchNo from sysbranchsubord t where t.branchno='"+ fromBranchNo + "' and SubBranchType='11')";
			fromSaleBrchNo=" a.FromSaleBranchNo in (select subBranchNo from sysbranchsubord t where t.branchno='"+ fromBranchNo + "' and SubBranchType='12')";			
		}
		String toIssueBrchNo =" a.ToIssueBranchNo ='" + toBranchNo + "'";
		String toSaleBrchNo = " a.ToSaleBranchNo ='" + toBranchNo + "'";
		if (ssSingle.equals("0")) {
			toIssueBrchNo=" a.ToIssueBranchNo in (select subBranchNo from sysbranchsubord t where t.branchno='"+ toBranchNo + "' and SubBranchType='11')";
			toSaleBrchNo=" a.ToSaleBranchNo in (select subBranchNo from sysbranchsubord t where t.branchno='"+ toBranchNo + "' and SubBranchType='12')";			
		}
		
		
		String sql="select distinct(a.OrderNumber),a.FromIssueBranchNo,a.ToIssueBranchNo,a.FromSaleBranchNo,a.ToSaleBranchNo,a.trantime, c.ordercount,c.cardstartseqno,c.cardendseqno,"
                  +"a.transferstatus,a.remark from TransferInOutStockOrder a,TransferInOutStockOrderDetail c,MakeCardControl d"
                  +" where  a.transferorderid=c.transferinoutstockorderid "
                  +" and c.batchno=d.batchno and d.issuecompanyno=?1 " ;
        if(!transferStatus.equals("10"))
    	       sql=sql+"  and a.transferstatus=?4 ";
        
               sql=sql+" and (("+fromSaleBrchNo+") or ("+fromIssueBrchNo+")) and (("+toSaleBrchNo+") or ("+toIssueBrchNo+"))"
                  +"  and a.TranTime>=to_date(?2,'yyyy-MM-dd hh24:mi:ss') and a.TranTime<=to_date(?3,'yyyy-MM-dd hh24:mi:ss')  order by a.OrderNumber desc";

		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuecompanyno);
		query.setParameter(2, startTime.trim()+" 00:00:00");
		query.setParameter(3, endTime.trim()+" 23:59:59");
		query.setParameter(4, transferStatus);
		List<Object[]> list = query.getResultList();
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<TransferInOutStockOrder> getTransferInOutStockOrderListByCond(
			PCPSWebInput webInput, String tosysbranch, String starttime,
			String endtime, String transferStatus, Integer page) {
		//System.out.println(tosysbranch+"--"+starttime+"--"+endtime+"--"+transferStatus+"--"+page);
		String sql = "select transferorderid,fromissuebranchno,toissuebranchno,fromsalebranchno,tosalebranchno,trantime,canceltransferorderid,transferstatus,issueoperid,confirmoperid,confirmtime,remark,confirmcancelremark,ordernumber "
				+ "from TransferInOutStockOrder where "
				+ " TranTime between to_date('"
				+ starttime
				+ "','yyyy-mm-dd hh24:mi:ss') and to_date('"
				+ endtime
				+ "','yyyy-mm-dd hh24:mi:ss') "
				+ "and (?1 is null or TransferStatus=?1) ";
			     if(webInput.isAll()==false){
				     sql+=" and (FromIssueBranchNo in (select subBranchNo from sysbranchsubord t where t.branchno=?2) or ToIssueBranchNo in (select subBranchNo from sysbranchsubord t where t.branchno=?2)"
				      +" or FromSaleBranchNo in (select subBranchNo from sysbranchsubord t where t.branchno=?2) or ToSaleBranchNo in (select subBranchNo from sysbranchsubord t where t.branchno=?2)"
				      +" )";
				     if(!PCPSUtil.isEmpty(tosysbranch)){
					      sql+=" and (FromIssueBranchNo in (select subBranchNo from sysbranchsubord t where t.branchno=?3) or ToIssueBranchNo in (select subBranchNo from sysbranchsubord t where t.branchno=?3)"
					      +" or FromSaleBranchNo in (select subBranchNo from sysbranchsubord t where t.branchno=?3) or ToSaleBranchNo in (select subBranchNo from sysbranchsubord t where t.branchno=?3))";
				     }
			     }else{
			    	if(!PCPSUtil.isEmpty(tosysbranch))
				       sql+="	and ((FromIssueBranchNo=?2 or ToIssueBranchNo=?2) and (FromIssueBranchNo=?3 or ToIssueBranchNo=?3))";
				    else
				    	sql+="	and (FromIssueBranchNo=?2 or ToIssueBranchNo=?2)"; 
				}
				sql+= " order by trantime desc";
		Query query = null;
		if(page<0)
		    query = _em.createNativeQuery(sql, TransferInOutStockOrder.class);
		else{
			int maxResult = Config.PAGE_SIZE;
			int startPosition = maxResult * page;
			query = _em.createNativeQuery(sql, TransferInOutStockOrder.class);
			query.setFirstResult(startPosition);
			query.setMaxResults(maxResult);
		}
		query.setParameter(1, transferStatus);
		query.setParameter(2, webInput.getOperBranchNo());
		query.setParameter(3, tosysbranch);
		
		return query.getResultList();
	}

	@Override
	public Integer getTransferInOutStockOrderTotalByCond(
			PCPSWebInput webInput,String tosysbranch, String starttime, String endtime,
			String transferStatus) {
		String sql = "select nvl(count(*),0) from TransferInOutStockOrder where"
			+ " TranTime between to_date('"
			+ starttime
			+ "','yyyy-mm-dd hh24:mi:ss') and to_date('"
			+ endtime
			+ "','yyyy-mm-dd hh24:mi:ss') "
			+ "and (?1 is null or TransferStatus=?1) ";
			if(webInput.isAll()==false){
			  sql+=" and (FromIssueBranchNo in (select subBranchNo from sysbranchsubord t where t.branchno=?2) or ToIssueBranchNo in (select subBranchNo from sysbranchsubord t where t.branchno=?2)"
			      +" or FromSaleBranchNo in (select subBranchNo from sysbranchsubord t where t.branchno=?2) or ToSaleBranchNo in (select subBranchNo from sysbranchsubord t where t.branchno=?2)"
			      +" )";
			     if(!PCPSUtil.isEmpty(tosysbranch)){
				      sql+=" and (FromIssueBranchNo in (select subBranchNo from sysbranchsubord t where t.branchno=?3) or ToIssueBranchNo in (select subBranchNo from sysbranchsubord t where t.branchno=?3)"
				      +" or FromSaleBranchNo in (select subBranchNo from sysbranchsubord t where t.branchno=?3) or ToSaleBranchNo in (select subBranchNo from sysbranchsubord t where t.branchno=?3))";
			     }
		     }else{
		    	if(!PCPSUtil.isEmpty(tosysbranch))
			       sql+="	and ((FromIssueBranchNo=?2 or ToIssueBranchNo=?2) and (FromIssueBranchNo=?3 or ToIssueBranchNo=?3))";
			    else
			    	sql+="	and (FromIssueBranchNo=?2 or ToIssueBranchNo=?2)"; 
			}
			
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, transferStatus);
		query.setParameter(2, webInput.getOperBranchNo());
		query.setParameter(3, tosysbranch);;
		return Integer.parseInt(query.getSingleResult().toString());
	}
}
